﻿package com.pb.news.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.pb.news.dao.BaseDao;
import com.pb.news.dao.NewsDao;
import com.pb.news.entity.News;

public class NewsDaoImpl extends BaseDao implements NewsDao {
	// 查询新闻信息
	public List<News> getNewsList(){
		List<News> newList=new ArrayList<News>();
		try {
			//（3）获得Statement对象，执行SQL语句
			String sql="select * from news_detail";
			Object[] params={};
			ResultSet rs=this.executeSQL(sql, params);
			//（4）处理执行结果(ResultSet)，
			while(rs.next()){
				int id=rs.getInt("id");
				String title=rs.getString("title");
				String summary=rs.getString("summary");
				String content=rs.getString("content");
				String author=rs.getString("author");
				Timestamp time=rs.getTimestamp("createDate");
				
				//封装成新闻信息对象
				News news=new News();
				news.setId(id);
				news.setTitle(title);
				news.setSummary(summary);
				news.setContent(content);
				news.setAuthor(author);
				news.setCreateDate(time);
				
				//将新闻对象放进集合中
				newList.add(news);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//释放资源
			this.closeResource();
		}
		return newList;
	}

	// 增加新闻信息
	public boolean add(News news) {
		boolean flag=false;
		try {
			String sql="insert into news_detail (categoryId,title,summary,content,createDate,author,picPath) values (?,?,?,?,?,?,?)";
			Object[] params={news.getCategoryId(),news.getTitle(),news.getSummary(),news.getContent(), news.getCreateDate(),news.getAuthor(),news.getPicPath()};
			int i=this.executeUpdate(sql, params);
			//（4）处理执行结果
			if(i>0){
				System.out.println("插入新闻成功！");
			}
			flag=true;
		}finally{
			//释放资源
			this.closeResource();
		}
		return flag;
	}
	
	// 删除新闻信息
	public boolean delete(News news) {
		boolean flag=false;
		try {
			String sql = "delete from  news_detail where id=?";
			Object[] params={news.getId()};
			int i=this.executeUpdate(sql, params);
			//（4）处理执行结果
			if(i>0){
				System.out.println("删除新闻成功！");
			}
			flag=true;
		}  finally {
			//释放资源
			this.closeResource();
		}
		return flag;
	}

	// 修改新闻
	public boolean update(News news) {
		boolean flag=false;
		try {
			String sql = "update news_detail set categoryId=? , title=?, summary=? , content=?, picPath=?"
		             +",author=? ,modifyDate=? where id=?";
			Object[] params={news.getCategoryId(),news.getTitle(),news.getSummary(),news.getContent(),news.getPicPath(),news.getAuthor(),news.getModifyDate(),news.getId()};
			int i=this.executeUpdate(sql, params);
			//（4）处理执行结果
			if(i>0){
				System.out.println("修改新闻成功！");
			}
			flag=true;
		}  finally {
			//释放资源
			this.closeResource();
		}
		return flag;
	}
	
	@Override
	public News getNewsById(int id) {
		// TODO Auto-generated method stub
		News news = new News();
		String sql = "Select n.*,c.name as categoryName from news_detail n,news_category c Where n.id = ? and c.id = n.categoryId";
		Object[] params = {id};
		if(getConnection()){
			ResultSet rs = this.executeSQL(sql, params);
			try {
				if(rs.next()){
					news.setId(rs.getInt("id"));
					news.setTitle(rs.getString("title"));
					news.setAuthor(rs.getString("author"));
					news.setContent(rs.getString("content"));
					news.setCategoryId(Integer.parseInt(rs.getString("categoryId")));
					news.setSummary(rs.getString("summary"));
					news.setPicPath(rs.getString("picPath"));
					news.setCreateDate(rs.getTimestamp("createDate"));
					news.setCategoryName(rs.getString("categoryName"));
					return news;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				this.closeResource();
			}
 		}
		return null;
	}
	
	@Override
	public int getNewsCount() {
		// TODO Auto-generated method stub
		int count = 0;
		String sql = "select count(*) as count from news_detail" ;
		Object[] params = {};
		if (getConnection()) {
			ResultSet rs = this.executeSQL(sql, params);
			try {
				if(rs.next()){
					count = rs.getInt("count");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				this.closeResource();
			}
		}
		return count;
	}
	
	@Override
	public List<News> getPagesNewsList(int pageNo, int pageSize) {
		// TODO Auto-generated method stub
		List<News> newsList = new ArrayList<News>();
		String sql = "select id,title,author,createDate from news_detail order by createDate desc limit ?,?";
		Object[] params = {(pageNo-1)*pageSize,pageSize};
		if(getConnection()){
			ResultSet rs = this.executeSQL(sql, params);
			try {
				while(rs.next()){
					News news = new News();
					news.setId(rs.getInt("id"));
					news.setTitle(rs.getString("title"));
					news.setCreateDate(rs.getTimestamp("createDate"));
					news.setAuthor(rs.getString("author"));
					newsList.add(news);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				this.closeResource();
			}
		}
		return newsList;
	}
	
	//测试
	public static void main(String[] args) {
		NewsDaoImpl newsDao=new NewsDaoImpl();
		News news = new News();
		//测试新闻增加
//		news.setTitle("快女选秀快讯");
//		news.setSummary("快女比赛正进入白热化");
//		news.setContent("她、她、她，谁是你心中的快女冠军？");
//		news.setAuthor("admin");
//		news.setCreateDate(new Date());
//		newsDao.add(news);
		
		//测试修改新闻标题
		news.setId(8);
		news.setTitle("快女选秀快讯修改");
		news.setAuthor("admin");
		news.setSummary("修改摘要");
		news.setCategoryId(1);
		news.setContent("修改内容");
		news.setModifyDate(new Date());
		newsDao.update(news);
		
		//测试删除新闻
//		news.setId(7);
//		newsDao.delete(news);
		
		List<News> newsList=newsDao.getNewsList();
		for(News _news:newsList){
			System.out.println(_news.getId()+"\t"+_news.getTitle()+"\t"+_news.getSummary()+"\t"+_news.getContent()+"\t"+_news.getAuthor()+"\t"+_news.getCreateDate());
		}
	}
}
